﻿using System;
using System.IO;
using NPOI;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using System.Web;

namespace QualityRpt.Utils
{
	public class ExcelUtils
	{
		public ExcelUtils ()
		{
		}
		/// <summary>
		/// 生成到excel
		/// </summary>
		/// <returns>The to excel.</returns>
		/// <param name="table">数据表</param>
		/// <param name="strTemplate">数据表模板.</param>
		/// <param name="startRowIndex">数据开始填充行</param>
		/// <param name="hasCaption">If set to <c>true</c> has caption.</param>
		public static MemoryStream RenderToExcelMs(DataTable table,string strTemplate,string sheetName
			,int startRowIndex,bool hasCaption,int headerRowIndex)
		{
			MemoryStream ms = new MemoryStream();
			using (table)
			{
				FileStream file = new FileStream(strTemplate, FileMode.Open, FileAccess.Read);//读入excel模板
				IWorkbook workbook = new HSSFWorkbook(file);
				ISheet sheet = workbook.GetSheetAt (0);
				if (sheetName != null && sheetName.Trim () != string.Empty) {
					sheet = workbook.GetSheet (sheetName);
				}
				// handling header.
				if(!hasCaption){
					IRow headerRow = sheet.GetRow(headerRowIndex);
					if (headerRow == null) {
						headerRow = sheet.CreateRow (headerRowIndex);
					}
					for (int i = 0; i < table.Columns.Count; i++) {
						ICell hc = headerRow.GetCell (i);
						if (hc == null) {
							hc = headerRow.CreateCell (i);
						}
						hc.SetCellValue (table.Columns [i].Caption);
					}
				}
				// handling value.
				int rowIndex = startRowIndex;

				foreach (DataRow row in table.Rows)
				{
					IRow dataRow = sheet.GetRow (rowIndex);
					if (dataRow == null) {
						dataRow = sheet.CreateRow (rowIndex);
					}
					for(int i=0;i<table.Columns.Count;i++){
						DataColumn dc = table.Columns [i];
						ICell dataCell = dataRow.GetCell (i);
						if (dataCell == null) {
							dataCell = dataRow.CreateCell (i);
						}
						if (row [i] != DBNull.Value && dc.DataType == typeof(decimal)) {
							dataCell.SetCellType (CellType.Numeric);
							dataCell.SetCellValue (Convert.ToDouble (row [i]));
						} else if (row [i] != DBNull.Value && dc.DataType == typeof(Int32)) {
							dataCell.SetCellType (CellType.Numeric);
							dataCell.SetCellValue (Convert.ToInt32 (row [i]));
						} else if (row [i] != DBNull.Value && dc.DataType == typeof(float)) {
							dataCell.SetCellType (CellType.Numeric);
							dataCell.SetCellValue (Convert.ToSingle (row [i]));
						} else if (row [i] != DBNull.Value && dc.DataType == typeof(double)) {
							dataCell.SetCellType (CellType.Numeric);
							dataCell.SetCellValue (Convert.ToDouble (row [i]));
						}else if(row[i]!=DBNull.Value) {
							dataCell.SetCellType (CellType.String);
							dataCell.SetCellValue (row [i].ToString ());
						}
					}
					rowIndex++;
				}
				workbook.Write(ms);
				ms.Flush();
				ms.Position = 0;
			}
			return ms;
		}
		public static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
		{
			if (context.Request.Browser.Browser == "IE")
			{
				fileName = HttpUtility.UrlEncode(fileName);
			}
			context.Response.ContentType = "application/vnd.ms-excel";
			context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
			context.Response.BinaryWrite(ms.ToArray());
		}
		public static void RenderToFile(MemoryStream ms,string fileName){
			FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate);
			BinaryWriter w = new BinaryWriter(fs);
			w.Write(ms.ToArray());
			fs.Close();
			ms.Close();
		}
	}
}

